package com.ssy.dao;

import com.ssy.common.PageInfo;
import com.ssy.entity.Score;
import com.ssy.vo.ScoreVo;

import java.util.HashMap;

/**
 * ScoreDao
 *
 * @author ssy
 * @date 2020/11/21
 **/
public class ScoreDao extends BaseDao {


    public PageInfo<ScoreVo> selectPage(HashMap<String, Object> params, Integer pageNo, Integer limitNo) {
        String sql = "SELECT sc.score_id AS scoreId,c.c_id AS cId,co.course_id AS courseId, s.number AS studentNumber,s.name AS studentName,s.sex AS studentSex,\n" +
                "m.majorName AS majorName,c.cname AS className,co.name AS courseName , sc.fraction AS fraction,\n" +
                "cu.teacher AS teacherName FROM student s INNER JOIN class c ON s.s_class=c.c_id INNER JOIN score sc ON sc.studentNum=s.s_id\n" +
                "INNER JOIN major m ON m.m_id=s.m_id  INNER JOIN course co ON co.course_id=sc.courseNum \n" +
                "INNER JOIN curriculum cu ON cu.cum_id=sc.cum_id where";
        if (params.containsKey("studentName")) {
            sql = sql + " s.name like '%" + params.get("studentName") + "%'  and";
        }
        if (params.containsKey("studentNumberId")) {
            sql = sql + " s.number = " + params.get("studentNumberId") + "  and";
        }
        if (params.containsKey("className")) {
            sql = sql + " c.cname like '%" + params.get("className") + "%'  and";
        }
        if (params.containsKey("teacherName")) {
            sql = sql + " cu.teacher like '%" + params.get("teacherName") + "%'  and";
        }
        if (params.containsKey("majorName")) {
            sql = sql + " m.m_id = " + params.get("majorName") + "  and";
        }
        if (params.containsKey("courseName")) {
            sql = sql + " co.course_id = " + params.get("courseName") + "  and";
        }
        if (sql.endsWith("and")) {
            sql = sql.substring(0, sql.lastIndexOf("and"));
        }
        if (sql.endsWith("where")) {
            sql = sql.substring(0, sql.lastIndexOf("where"));
        }
        return this.selectPage(sql, ScoreVo.class, pageNo, limitNo);
    }

    public boolean insertScore(int studentId, int courseNameIdNo, int cumId, int fractionNo) {
        String sql = "INSERT INTO score(studentNum,courseNum,cum_id,fraction) VALUE(?,?,?,?)";
        return this.update(sql, studentId, courseNameIdNo, cumId, fractionNo);
    }


    public boolean modifyScore(int studentId, int courseNameIdNo, int cumId, int fractionNo, int scoreIdNo) {
        String sql = "UPDATE score SET studentNum=?,courseNum=?,cum_id=?,fraction=? WHERE score_id=?";
        return this.update(sql, studentId, courseNameIdNo, cumId, fractionNo, scoreIdNo);
    }

    public boolean batchDeleteScore(String[] ids) {
        String sql = "delete from score where score_id=?";
        return this.batchDelete(sql, ids);
    }

    public boolean deleteOneScore(int id) {
        String sql = "delete from score where score_id=?";
        return this.update(sql, id);
    }

    public Score queryScoreBystudentIdAndCourseId(int studentId, int courseNameIdNo) {
        String sql = "SELECT score_id as scoreId FROM score WHERE studentNum=? AND courseNum=?";
        return this.selectOne(sql, Score.class, studentId, courseNameIdNo);
    }
}
